package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import bean.Algorithm;

/**
 * The database reader that retrieve related data from the database.
 * 
 * @author yexijiang
 * 
 */
public class DBReader {

	private Connection connection;
	private static HashMap<String,String> mapData = new HashMap<String,String>();
	static{
		mapData.put("shop","车间");
		mapData.put("flow","工序");
		mapData.put("flow_desc", "工序描述");
		mapData.put("oper","工位");
		mapData.put("oper_desc","工位描述");
		mapData.put("equip_id","设备代码");
		mapData.put("equip_desc","设备描述");
		mapData.put("char_id","设备参数代码");
		mapData.put("char_desc","工艺参数描述");
		mapData.put("unit","单位");
		mapData.put("lsl","规格下限");
		mapData.put("usl","规格上限");
		mapData.put("target","目标值");
		mapData.put("ess","重要性");
	}
	public DBReader() {
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://131.94.130.37:31313/pdp?useUnicode=true&characterEncoding=utf8";
			String username = "pdp";
			String password = "pdp";
			connection = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public String test() {
		return "test";
	}

	public List<Algorithm> getAllAlg() {
		try {
			List<Algorithm> algList = new ArrayList<Algorithm>();
			Statement stmt = connection.createStatement();
			// list all algorithms
			String listAlgos = "select opID,opName,decription from operation";
			ResultSet rs = stmt.executeQuery(listAlgos);
			while (rs.next()) {
				Algorithm alg = new Algorithm();
				alg.setId(rs.getInt("opID"));
				alg.setName(rs.getString("opName"));
				alg.setDescription(rs.getString("decription"));
				algList.add(alg);
			}
			stmt.close();
			return algList;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	public List<String> getDescriptionByAlgID(String id) {
		try {
			List<String> result = new ArrayList<String>();
			Statement stmt = connection.createStatement();
			String query = "select decription, input, output from operation where opId="
					+ id;
			ResultSet rs = stmt.executeQuery(query);
			StringBuffer sb = new StringBuffer();
			while (rs.next()) {
				result.add(rs.getString("decription"));
				result.add(rs.getString("input"));
				result.add(rs.getString("output"));
			}
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<String> getFeaturesByAlgID(String algoId) {
		try {
			List<String> featureList = new ArrayList<String>();
			Statement stmt = connection.createStatement();
			String listFeatures = "select char_id from operation_character where opId = "
					+ algoId;
			ResultSet rs = stmt.executeQuery(listFeatures);
			while (rs.next())
				featureList.add(rs.getString("char_id"));
			stmt.close();
			return featureList;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Map<String, String> getFeatureByID(String featureId) {
		String query = "select * from char_specification where char_id='"
				+ featureId + "'";
		Map<String, String> detailMap = new HashMap<String, String>();
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			ResultSetMetaData rsMetaData = rs.getMetaData();
			int numberOfColumns = rsMetaData.getColumnCount();
			while (rs.next()) {
				for (int i = 1; i < numberOfColumns; ++i) {
					String columnName = rsMetaData.getColumnName(i);
					String value = rs.getString(columnName);
					System.out.println(columnName+"="+value);
					value = value == null ? "" : value;
					detailMap.put(mapData.get(columnName), value);
				}
			}
			return detailMap;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	public void close() throws Exception {
		if (this.connection == null)
			return;
		else
			connection.close();
	}

	public static void main(String args[]) {
		DBReader r = new DBReader();
		r.getFeatureByID("C112MA010101-007");
	}
}
